package insight.miescor.db;

import insight.common.Encrypter;
import insight.common.logging.JLogger;
import insight.miescor.opp.domain.AssignmentGrid;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.sormula.Database;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class DBManager {

	private Database database;
	private Connection connection;
	private static Logger logger = JLogger.getLogger("StratupLog");

	private DriverManagerDataSource dataSource;
	private JdbcTemplate template;

	public DBManager(String driver, String url, String user, String password) {
		try {
			Class.forName(driver);
			System.out.println("URL : " + url);
			System.out.println("user : " + user);
			System.out.println("password : " + password);
			connection = DriverManager.getConnection(url, user, password);
			database = new Database(connection);

			dataSource = new DriverManagerDataSource();
			dataSource.setDriverClassName(driver);
			dataSource.setUrl(url);
			dataSource.setUsername(user);
			dataSource.setPassword(decrypt(password));
			template = new JdbcTemplate(dataSource);
			System.out.println("connect");
		} catch (ClassNotFoundException e) {
			logger.log(Level.SEVERE,
					"Application startup failed, driver class " + driver
							+ " could not be loaded.", e);
		} catch (SQLException e) {
			logger.log(Level.SEVERE,
					"Application startup failed, connection to db failed.", e);
		}
	}

	private String decrypt(String inStr) {
		try {
			return Encrypter.decrypt(inStr);
		} catch (Exception ex) {

		}
		return inStr;
	}

	public Database getDatabase() {
		return database;
	}

	@SuppressWarnings("unchecked")
	public List<AssignmentGrid> getAssignmentList(String[] status,
			String[] roles) {
		List<AssignmentGrid> listAssignmentBeans = new ArrayList<AssignmentGrid>();
		AssignmentMapper newMapper = new AssignmentMapper();
		String sql = "Select Assignment.id,Assignment.opportunityid, Assignment.startdate,Assignment.enddate,Assignment.status,";
		sql += " Assignment.stepid,Assignment.role,opportunity.title,opportunity.projecttype,Assignment.userId,Assignment.ProjectObjId,";
		sql += " opportunity.initiator,opportunity.createdOn from Assignment inner Join opportunity on ";
		sql += " Assignment.opportunityid= opportunity.id where Assignment.status in ";
		sql += getStringInClause(status) + " And Assignment.role in "
				+ getStringInClause(roles);
		// System.out.println(sql);
		listAssignmentBeans = (List<AssignmentGrid>) template.query(sql,
				newMapper);
		// logger.fine(sql);

		return listAssignmentBeans;
	}

	@SuppressWarnings("unchecked")
	public List<AssignmentGrid> getClaimedAssignmentList(String[] status,
			String[] roles, String userId) {
		List<AssignmentGrid> listAssignmentBeans = new ArrayList<AssignmentGrid>();
		AssignmentMapper newMapper = new AssignmentMapper();
		String sql = "Select Assignment.id,Assignment.opportunityid, Assignment.startdate,Assignment.enddate,Assignment.status,";
		sql += " Assignment.stepid,Assignment.role,opportunity.title,opportunity.projecttype,Assignment.userId,Assignment.ProjectObjId, ";
		sql += " opportunity.initiator,opportunity.createdOn from Assignment inner Join opportunity on ";
		sql += " Assignment.opportunityid= opportunity.id where Assignment.status in ";
		sql += getStringInClause(status) + " And Assignment.role in "
				+ getStringInClause(roles);
		sql += " And Assignment.userId in ('" + userId + "')";
		listAssignmentBeans = (List<AssignmentGrid>) template.query(sql,
				newMapper);
		// logger.fine(sql);
		return listAssignmentBeans;
	}

	public String getStringInClause(String[] ary) {
		String clause = "(";
		for (String st : ary) {
			clause += "'" + st + "',";
		}
		if (clause.lastIndexOf(",") > 0)
			clause = clause.substring(0, clause.lastIndexOf(","));
		clause += ") ";
		return clause;
	}

	public JdbcTemplate getTemplate() {
		return template;
	}

	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}

}
